"""Initial migration - create product tables

Revision ID: 998f58874cd6
Revises: 
Create Date: 2025-10-05 11:53:30.271760

"""
from typing import Sequence, Union

from alembic import op
import sqlalchemy as sa
from sqlalchemy.sql import table, column
from sqlalchemy import String, Integer, Numeric, Boolean


# revision identifiers, used by Alembic.
revision: str = '998f58874cd6'
down_revision: Union[str, Sequence[str], None] = None
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None


def upgrade() -> None:
    """Upgrade schema."""
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table('product_categories',
    sa.Column('id', sa.Integer(), nullable=False, comment='分类ID'),
    sa.Column('name', sa.String(length=100), nullable=False, comment='分类名称'),
    sa.Column('description', sa.Text(), nullable=True, comment='分类描述'),
    sa.Column('parent_id', sa.Integer(), nullable=True, comment='父分类ID，支持层级分类'),
    sa.Column('sort_order', sa.Integer(), nullable=True, comment='排序顺序'),
    sa.Column('is_active', sa.Integer(), nullable=True, comment='是否激活：1-激活，0-禁用'),
    sa.Column('created_at', sa.DateTime(timezone=True), server_default=sa.text('now()'), nullable=True, comment='创建时间'),
    sa.Column('updated_at', sa.DateTime(timezone=True), server_default=sa.text('now()'), nullable=True, comment='更新时间'),
    sa.PrimaryKeyConstraint('id'),
    comment='商品分类表'
    )
    op.create_index(op.f('ix_product_categories_id'), 'product_categories', ['id'], unique=False)

    op.create_table('products',
    sa.Column('id', sa.Integer(), nullable=False, comment='商品ID'),
    sa.Column('name', sa.String(length=200), nullable=False, comment='商品名称'),
    sa.Column('description', sa.Text(), nullable=True, comment='商品描述'),
    sa.Column('price', sa.Numeric(precision=10, scale=2), nullable=False, comment='商品价格'),
    sa.Column('original_price', sa.Numeric(precision=10, scale=2), nullable=True, comment='原价'),
    sa.Column('stock', sa.Integer(), nullable=True, comment='库存数量'),
    sa.Column('sales', sa.Integer(), nullable=True, comment='销量'),
    sa.Column('category_id', sa.Integer(), nullable=False, comment='商品分类ID'),
    sa.Column('image_url', sa.String(length=500), nullable=True, comment='商品图片URL'),
    sa.Column('sku', sa.String(length=100), nullable=False, comment='商品SKU'),
    sa.Column('is_active', sa.Boolean(), nullable=True, comment='是否上架'),
    sa.Column('is_featured', sa.Boolean(), nullable=True, comment='是否推荐商品'),
    sa.Column('created_at', sa.DateTime(timezone=True), server_default=sa.text('now()'), nullable=True, comment='创建时间'),
    sa.Column('updated_at', sa.DateTime(timezone=True), server_default=sa.text('now()'), nullable=True, comment='更新时间'),
    sa.ForeignKeyConstraint(['category_id'], ['product_categories.id'], ),
    sa.PrimaryKeyConstraint('id'),
    sa.UniqueConstraint('sku'),
    comment='商品表'
    )
    op.create_index(op.f('ix_products_id'), 'products', ['id'], unique=False)

    # 插入默认分类数据
    product_categories_table = table('product_categories',
        column('id', Integer),
        column('name', String),
        column('description', String),
        column('sort_order', Integer),
        column('is_active', Integer)
    )

    op.bulk_insert(product_categories_table, [
        {'id': 1, 'name': '电子产品', 'description': '各种电子设备和配件', 'sort_order': 0, 'is_active': 1},
        {'id': 2, 'name': '服装鞋帽', 'description': '时尚服装、鞋子和帽子', 'sort_order': 0, 'is_active': 1},
        {'id': 3, 'name': '家居用品', 'description': '家庭生活用品', 'sort_order': 0, 'is_active': 1},
        {'id': 4, 'name': '食品饮料', 'description': '各种食品和饮料', 'sort_order': 0, 'is_active': 1},
        {'id': 5, 'name': '图书音像', 'description': '书籍、音乐和影视产品', 'sort_order': 0, 'is_active': 1},
    ])

    # 插入示例商品数据
    products_table = table('products',
        column('id', Integer),
        column('name', String),
        column('description', String),
        column('price', Numeric),
        column('original_price', Numeric),
        column('stock', Integer),
        column('sales', Integer),
        column('category_id', Integer),
        column('image_url', String),
        column('sku', String),
        column('is_active', Boolean),
        column('is_featured', Boolean)
    )

    op.bulk_insert(products_table, [
        {
            'id': 1,
            'name': 'iPhone 15 Pro',
            'description': '苹果最新旗舰手机，搭载A17 Pro芯片',
            'price': 7999.00,
            'original_price': 8999.00,
            'stock': 100,
            'sales': 50,
            'category_id': 1,
            'image_url': '/static/images/iphone15pro.jpg',
            'sku': 'IPHONE15PRO128GB',
            'is_active': True,
            'is_featured': True
        },
        {
            'id': 2,
            'name': '小米电视 65寸',
            'description': '4K超高清智能电视，支持语音控制',
            'price': 2999.00,
            'original_price': 3499.00,
            'stock': 50,
            'sales': 20,
            'category_id': 1,
            'image_url': '/static/images/mitv65.jpg',
            'sku': 'MITV65INCH4K',
            'is_active': True,
            'is_featured': False
        },
        {
            'id': 3,
            'name': 'Nike运动鞋',
            'description': '舒适透气的跑步鞋，适合日常运动',
            'price': 599.00,
            'original_price': 799.00,
            'stock': 200,
            'sales': 150,
            'category_id': 2,
            'image_url': '/static/images/nike-shoes.jpg',
            'sku': 'NIKE-SHOES-42',
            'is_active': True,
            'is_featured': True
        },
        {
            'id': 4,
            'name': '床上四件套',
            'description': '纯棉材质，柔软舒适的床上用品',
            'price': 299.00,
            'original_price': 399.00,
            'stock': 80,
            'sales': 60,
            'category_id': 3,
            'image_url': '/static/images/bedding-set.jpg',
            'sku': 'BED-SET-KING',
            'is_active': True,
            'is_featured': False
        },
        {
            'id': 5,
            'name': '进口咖啡豆',
            'description': '哥伦比亚进口，口感香醇浓郁',
            'price': 89.00,
            'original_price': 109.00,
            'stock': 300,
            'sales': 200,
            'category_id': 4,
            'image_url': '/static/images/coffee-beans.jpg',
            'sku': 'COFFEE-BEANS-500G',
            'is_active': True,
            'is_featured': False
        },
        {
            'id': 6,
            'name': 'Python编程从入门到精通',
            'description': '零基础学习Python编程的完整指南',
            'price': 79.00,
            'original_price': 99.00,
            'stock': 150,
            'sales': 300,
            'category_id': 5,
            'image_url': '/static/images/python-book.jpg',
            'sku': 'BOOK-PYTHON-BEGINNER',
            'is_active': True,
            'is_featured': True
        }
    ])
    # ### end Alembic commands ###


def downgrade() -> None:
    """Downgrade schema."""
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_index(op.f('ix_products_id'), table_name='products')
    op.drop_table('products')
    op.drop_index(op.f('ix_product_categories_id'), table_name='product_categories')
    op.drop_table('product_categories')
    # ### end Alembic commands ###
